Identity columns in SQL Server are one of the simplest ways to generate unique values. Define a column as IDENTITY(1,1), and SQL Server will happily produce 1, 2, 3, 4… as new rows are inserted. For primary keys, this works beautifully.
But problems arise when developers use Identity values for invoice numbers, accounting documents, receipts, or any sequence that must be strictly ordered and gap‑free. At first glance, Identity looks perfect. In reality, it is not designed for financial or legal numbering at all. Why!?
Identity Guarantees Uniqueness — Not Continuity
SQL Server only guarantees that Identity values are unique.
It does not guarantee that:
- numbers are sequential
- numbers are gap‑free
- numbers are predictable
- numbers reflect business rules
Why Identity Values Produce Gaps
Gaps in Identity values are normal and expected. They happen for several technical reasons:
- Rollbacks and Failed Transactions
SQL Server allocates the next Identity value before the transaction commits. If the transaction rolls back, the Identity value is lost forever.
Example:
- Transaction inserts row → Identity = 2
- Transaction rolls back
- Next insert → Identity = 3
Result: 2 disappears permanently. - DELETE Operations
If a row is deleted, SQL Server does not reuse its Identity value. This is intentional to preserve uniqueness and avoid race conditions. - Identity Cache Behavior
Starting with SQL Server 2012, Identity values are cached in memory for performance.
If:
- SQL Server restarts
- the service crashes
- the machine reboots
the cached Identity values are lost, creating gaps. This is not a bug — it’s an optimization. - Parallel Inserts
Under heavy concurrency, multiple sessions may reserve Identity values simultaneously. This can also lead to unexpected jumps.
Why This Is a Problem for Financial Systems
- In accounting and financial workflows, numbering must be:
- sequential
- gap‑free
- auditable
- legally compliant
- predictable - A missing invoice number can trigger:
- audit flags
- compliance issues
- suspicion of fraud
- reconciliation errors
Identity simply cannot meet these requirements.
Recommended Alternatives for Gap‑Free Numbering
For systems where numbering matters, you need controlled, transactional, deterministic mechanisms.
Here are the reliable options:
- Number Generator Table (Most Common & Reliable)
Create a table that stores the last used number:
CREATE TABLE InvoiceNumberGenerator (
Id INT PRIMARY KEY,
LastNumber BIGINT
);
Then:
- lock the row
- increment the number
- return the new value
- commit the transaction
This guarantees no gaps and full control. - Sequence Object With Transaction Control
A SQL Server Sequence can work if:
- you wrap number generation in a strict transaction
- you avoid rollbacks after consuming a number
- you serialize access
By itself, a Sequence also produces gaps — but with proper locking logic, it can be controlled. - Application‑Level Numbering
Many ERP and accounting systems generate numbers in the application layer.
This allows:
- business rules
- prefixes and series
- fiscal year resets
- multi‑branch numbering
This is often the most flexible approach.
- Stored Procedure for Controlled Number Assignment
A dedicated stored procedure can:
- lock a generator row
- produce the next number
- validate business rules
- commit atomically
This pattern is widely used in banking and financial systems.
Seyed Hamed Vahedi
Fri, 19 December, 2025